/*
 * WapPortalBean.java
 *
 * Created on May 11, 2007, 3:12 PM
 */
package wap.rockvilletech.portal;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.io.Serializable;
import java.net.HttpURLConnection;
import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Vector;

import net.sourceforge.wurfl.core.Device;

import org.apache.log4j.Logger;


/**
 * @author Rock
 */
@SuppressWarnings("serial")
public class WapPortalBean implements Serializable {

    private static final Logger log = Logger.getLogger(WapPortalBean.class);
    private String[] metaData;
    private String autoGenID;
    private Device wurflDevice;

    public WapPortalBean() {
    }
    
    public WapPortalBean(Device dv) {
    	wurflDevice = dv;
    }
    
    public void setWurflDevice(Device dv) {
    	wurflDevice = dv;
    }
    
    private void setMetaData(String[] mData) {
        metaData = mData;
    }

    private void setAutoGenID(String value) {
        autoGenID = value;
    }

    public String[] getMetaData() {
        return metaData;
    }

    public String getAutoGenID() {
        return autoGenID;
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="Generic DB Functions, Used to assist other functions">
    private Vector execQuery_Direct(String methodName, String queryToBeExecuted, HashMap<String, String> hmParam) {
        return execQuery(methodName, queryToBeExecuted, "", hmParam, false, true);
    }

    /**
     * <pre>
     * This function fetches the results from Database.
     * It sets error message, if it encounters any problem
     * </pre>
     * @param methodName
     * @param queryToBeExecuted
     * @param hmParam
     * @return vector
     */
    private Vector execQuery(String methodName, String queryToBeExecuted, HashMap<String, String> hmParam) {
        return execQuery(methodName, queryToBeExecuted, "", hmParam, false);
    }

    private Vector execQuery(String methodName, String queryToBeExecuted, String server, HashMap<String, String> hmParam, boolean skipParamCheck) {
        return execQuery(methodName, queryToBeExecuted, server, hmParam, skipParamCheck, false);
    }

    /**
     * <pre>
     * This function fetches the results from Database.
     * It sets error message, if it encounters any problem
     * </pre>
     * @param methodName
     * @param queryToBeExecuted
     * @param server
     * @param hmParam
     * @param skipParamCheck
     * @param isDirect Determines weather a query is passed or a query_name is passed
     * @return vector
     */
    private Vector execQuery(String methodName, String queryToBeExecuted, String server, HashMap<String, String> hmParam,
            boolean skipParamCheck, boolean isDirect) {
        Vector $v = null;
        Vector $columnLabels = null;
        String[] $cols = null;
        DBProc $dbp = null;

        $dbp = DBProc.getInstance();

        if ($dbp != null) {
            try {
                if (isDirect) {
                    $v = $dbp.executeDirectSQL(queryToBeExecuted, hmParam);
                } else {
                    $v = $dbp.executeSQLMethod(queryToBeExecuted, hmParam, skipParamCheck);
                }

                if ($v == null || $v.size() < 1 || $v.get(0).toString().equalsIgnoreCase("true") || $v.get(0).toString().equalsIgnoreCase("false") || $v.get(0).toString().equalsIgnoreCase("")) {
                    $v = null;
                    log.debug(" No Records Returned for " + methodName + ".");
                } else {
                    log.debug(methodName + " - " + $v.size() + " Records succesfully returned.");
                }
                // get the meta Data
                $columnLabels = $dbp.getMetaDataColumnLabels();
                try {
                    $cols = wap.rockvilletech.util.VectorUtil.vectorToString($columnLabels, 0);
                } catch (Exception ex) {
                    log.error(queryToBeExecuted, ex);
                }
                setMetaData($cols);
            } catch (Exception ex) {
                $v = null;
                log.error(" - ", ex);
                //ex.printStackTrace();
            } finally {
                $dbp = null;
                $columnLabels = null;
            }
        } else {
            $v = null;
            log.info("Cannot get reference to the Object DBProc");
        }
        if ($v == null) {
            $v = new Vector();
        }
        return $v;
    }

    /**
     *  <pre>
     * Executes INSERT and UPDATE statments.
     * </pre>
     * @param methodName
     * @param queryToBeExecuted
     * @param hmParam
     * @return vector <br>1 - null, if the execution fails<br>2 - a vector of vector containing ID(s) and true
     */
    private boolean execNonQuery(String methodName, String queryToBeExecuted, HashMap<String, String> hmParam) {
        return execNonQuery(methodName, queryToBeExecuted, "", hmParam, false);
    }

    /**
     * <pre>
     * Executes INSERT and UPDATE statments.
     * </pre>
     * @param methodName
     * @param queryToBeExecuted
     * @param server
     * @param hmParam
     * @param skipParamCheck
     * @return vector <br>1 - null, if the execution fails<br>2 - a vector of vector containing ID(s) and true
     */
    private boolean execNonQuery(String methodName, String queryToBeExecuted, String server, HashMap<String, String> hmParam,
            boolean skipParamCheck) {
        boolean $result = false;
        Vector $v = null;
        DBProc $dbp = null;

        $dbp = DBProc.getInstance();

        if ($dbp != null) {
            try {
                $v = $dbp.executeSQLMethod(queryToBeExecuted, hmParam, skipParamCheck);
                if ($v.get(0).toString().equalsIgnoreCase("true")) {
                    $result = true;
                    setAutoGenID($v.get(1).toString());
                } else {
                    $result = false;
                }
            } catch (Exception ex) {
                $v = null;
                log.error(queryToBeExecuted, ex);
            } finally {
                $v = null;
                $dbp = null;
            }
        } else {
            $v = null;
            log.warn("Cannot get reference to the Object DBProc");
        }

        return $result;
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="Static Functions">
    /**
     * <pre>
     * get the response of the URL.
     * </pre>
     * @param ULRStr
     * @return
     */
    public String getURLResponse(String ULRStr) {

        String URLresult = "";
        HttpURLConnection connection = null;
        BufferedReader rd  = null;
        StringBuilder sb = null;
        String line = null;
        
        try {
            URL url = new URL(ULRStr);
            connection = (HttpURLConnection)url.openConnection();
            connection.setDoOutput(true);
            connection.setReadTimeout(10000);
            
            rd  = new BufferedReader(new InputStreamReader(connection.getInputStream()));
            sb = new StringBuilder();
          
            while ((line = rd.readLine()) != null) {
                sb.append(line + '\n');
            }
            URLresult = sb.toString();
            rd.close();
        } catch (Exception exc) {
            log.error("getURLResponse(" + ULRStr + ") ", exc);
        } finally {
            //close the connection, set all objects to null
        	try {
	            connection.disconnect();
	            rd = null;
	            sb = null;
	            connection = null;
        	} catch(Exception e) {}
        }
        return URLresult;
    } //end method

    /*
    public String sendSMS(String msisdn, String text) {
    	return sendWappush(msisdn, text, null, false);    	
    }

    public String chargeInvisible(String msisdn) {
    	return sendWappush(msisdn, "", null, true);    	
    }
    
    public String sendWappush(String msisdn, String text, String link) {
    	return sendWappush(msisdn, text, link, false);
    }
    
    public String sendWappush(String msisdn, String text, String link, boolean invisible) {
    	
    	//http://172.16.23.36:8800/?Sender=210&PhoneNumber=%2B923335582023&&smscroute=Ufone212&Text=Thanks&pid=40
        String URLresult = "HTTP_ERROR";
        StringBuffer wapPushURL = new StringBuffer();
        wapPushURL.append(SystemProperty.getProperty("wappush_server_url"));
        if(invisible) {
        	wapPushURL.append("?Sender=210");
	        wapPushURL.append("&PhoneNumber=%2B"+msisdn);            
	        wapPushURL.append("&smscroute=Ufone212&pid=40");
        }
        else {
	        wapPushURL.append("?Sender="+SystemProperty.getProperty("sms_sender_code"));
	        wapPushURL.append("&PhoneNumber=%2B"+msisdn);            
	        wapPushURL.append("&smscroute="+SystemProperty.getProperty("smsc_route"));
        }
        wapPushURL.append("&Text="+text.replaceAll(" ", "%20"));
        if(link != null && !link.isEmpty()) {
        	wapPushURL.append("&WAPURL="+link);
        }
        String wpURL = wapPushURL.toString();
        try {        	        	
            URL url = new URL(wpURL);
            HttpURLConnection conn = (HttpURLConnection)url.openConnection();
            conn.setRequestMethod("GET");
            conn.connect();
            if(conn.getResponseCode() == HttpURLConnection.HTTP_OK) {
            	URLresult = "HTTP_OK";
            }
            conn.disconnect();
        } catch (Exception exc) {
        	log.info("Wappush URL (" + wpURL +")");
            log.error("sendWappush(" + msisdn + ", "+text+", "+link+", "+invisible+") ", exc);
        }
        return URLresult;        
    }
    */
    
    /**
     * <pre>
     * Process the GSM number passed, remove invalid characters.
     * And return the result.
     * Valid Number format is "92xxxxxxxxxx"
     * </pre>
     * @param gsmNumber
     * @return Valid GSMNumber OR any of the following Error Code
     * <pre>
     *  0 :  NULL or No Argument passed
     * -1 :  General Processing Error
     * -2 :  Not a Valid GSM Number
     * </pre>
     */
    public static String processGsmNumber(String gsmNumber) {
        String newGSM = gsmNumber;

        if (newGSM == null || newGSM.equalsIgnoreCase("NULL") || newGSM.length() == 0) {
            return "0";
        }

        try {
            if (newGSM.startsWith("+")) {
                newGSM = newGSM.substring(1);
            }
            if (newGSM.startsWith("00")) {
                newGSM = newGSM.substring(2);
            }

            if (newGSM.length() == 12) {
            } 
            else {
                if (newGSM.length() == 10 && newGSM.indexOf("92") != 0) {
                    newGSM = "92" + newGSM;
                } else if(newGSM.length()==11 && newGSM.startsWith("0")){
                    newGSM = newGSM.substring(1);
                    newGSM = "92" + newGSM;
                } else {
                    newGSM = "-2";
                }
            }

        } catch (Exception exc) {
            newGSM = "-1";
        }

        return newGSM.trim();
    } //end method

    public static String unidecode(String p_strUString) {
        if (p_strUString == null || p_strUString.length() < 4) {
            return " ";
        }

        StringBuffer strbufResult = new StringBuffer();

        String part = p_strUString;
        String uniChar = "";

        int rowEnd = part.indexOf("u", 1);

        do {
            uniChar = part.substring(1, rowEnd);
            part = part.substring(rowEnd);

            try {
                short valueAsShort = Short.parseShort(uniChar.trim(), 16);
                strbufResult.append(String.valueOf((char) valueAsShort));
            } catch (NumberFormatException ex) {
                log.error("uniChar: " + uniChar, ex);
            }
            rowEnd = part.indexOf("u", 1);
        } while (rowEnd > -1);

        uniChar = part.substring(1);
        try {
            short valueAsShort = Short.parseShort(uniChar.trim(), 16);
            strbufResult.append(String.valueOf((char) valueAsShort));
        } catch (NumberFormatException ex) {
            log.error("uniChar: " + uniChar, ex);
        }

        rowEnd = part.indexOf("u", 1);

        return strbufResult.toString();
    }

    public static String uniencode(String strInput) {
        StringBuffer sbuf = new StringBuffer();

        try {
            strInput = new String(strInput.getBytes("ISO8859_1"), "UTF8");

            char[] chars = strInput.toCharArray();
            sbuf.append("__UNI__");

            for (int i = 0; i < chars.length; i++) {
                String strNum = Integer.toHexString((int) chars[i]);
                if (strNum.length() == 1) {
                    sbuf.append("u000");
                } else if (strNum.length() == 2) {
                    sbuf.append("u00");
                } else if (strNum.length() == 3) {
                    sbuf.append("u0");
                }
                sbuf.append(strNum);
            }
        } catch (Exception e) {
        }

        return sbuf.toString();
    }

    // </editor-fold>
    // <editor-fold defaultstate="collapsed" desc="misc">
    /**
     * 
     * @param queryToBeExecuted
     * @param $hmparam
     * @return
     */
    public Vector execCustomQuery(String queryToBeExecuted, HashMap $hmparam) {
        return execQuery("execCustomQuery(" + queryToBeExecuted + ")", queryToBeExecuted, $hmparam);
    }

    // </editor-fold>
    /**
     * To get details of contentID
     * @param seqid
     * @return
     * By Aashiqbhatti
     */
    public ArrayList getContentDetail(String contentId) {

        ArrayList arrList = new ArrayList();

        try {
            HashMap hm = new HashMap();
            Vector v;
            hm.put("ContentID", contentId);

            v = this.execQuery("getContentDetail", "GETCONTENTDETAILS", hm);
            Vector tmp = null;
            String value = "";
            for (int i = 0; i < v.size(); i++) {
                tmp = (Vector) v.get(i);
                for (int j = 0; j < tmp.size(); j++) {
                    if (tmp.get(j) != null) {
                        value = tmp.get(j).toString();
                        if (value != null && !value.equalsIgnoreCase("")) {
                        } else {
                            value = "0";
                        }
                        arrList.add(value);

                    } else {
                        arrList.add("0");
                    }
                } //end inner for              
            }//end for            
        } catch (Exception exc) {
            log.error("ContentID: " + contentId, exc);
        }
        return arrList;
    }//end method

    /**
     * By Bilal
     * @param cid
     * @return
     */
    public Vector getContentDetails(String cid) {
        Vector vReturn = null;
        Connection con = null;
        DBProc db = DBProc.getInstance();
        try {
            String strQuery = "Select Contentimage,ContentName,Price,CategoryName,CategoryID,Description FROM content WHERE contentid = ? and Status = 1 limit 1";
            con = db.getJNDIConnection();
            PreparedStatement pst = con.prepareStatement(strQuery);
            pst.setString(1, cid);
            ResultSet rs = pst.executeQuery();
            vReturn = this.formatVector(rs);
            pst.close();
            rs.close();
        } catch (Exception e) {
            log.error(" getContentDetails() : cid =" + cid, e);
        } finally {
            db.closeJNDIConnection(con);
        }
        return vReturn;
    }

    /**
     * <pre>
     * prepares the result set to be returned as ArrayList.
     *
     * By: Usman Malik
     * May 11, 2009
     * </pre>
     *
     * @param rsData
     * @param arryOld
     * @return
     */
    private ArrayList<Object> formatArrayList(ResultSet rsData) {

        ArrayList<Object> arryOld = null;
        try {
            arryOld = new ArrayList<Object>();
            int cols = rsData.getMetaData().getColumnCount();
            while (rsData.next()) {
                ArrayList<Object> arryRow = new ArrayList<Object>();
                for (int i = 1; i <= cols; i++) {
                    arryRow.add(rsData.getObject(i));
                }
                arryOld.add(arryRow);
            }
        } catch (Exception ex) {
            arryOld = null;
        }
        return arryOld;
    }

    private Vector<Object> formatVector(ResultSet rsData) {

        Vector<Object> vecOld = null;
        try {
            vecOld = new Vector<Object>();
            int cols = rsData.getMetaData().getColumnCount();
            while (rsData.next()) {
                Vector<Object> vecRow = new Vector<Object>();
                for (int i = 1; i <= cols; i++) {
                    vecRow.add(rsData.getObject(i));
                }
                vecOld.add(vecRow);
            }
        } catch (Exception ex) {
            log.error("Error populating vector from db data", ex);
            vecOld = null;
        }
        return vecOld;
    }

    /**
     * To get details of contentID
     * @param seqid
     * @return
     * By Aashiqbhatti
     */
    public ArrayList getContentData(String seqid) {

        ArrayList arrList = new ArrayList();

        try {
            DBProc dbp = DBProc.getInstance();
            HashMap hm = new HashMap();
            Vector v;
            hm.put("SeqID", seqid);

            v = this.execQuery("getContentData", "GETCONTENTDATA", hm);

            for (int i = 0; i < v.size(); i++) {
                Vector tmp = (Vector) v.get(i);
                for (int j = 0; j < tmp.size(); j++) {
                    if (tmp.get(j) != null) {
                        String value = tmp.get(j).toString();
                        if (value != null && !value.equalsIgnoreCase("")) {
                        } else {
                            value = "0";
                        }
                        arrList.add(value);
                    } else {
                        arrList.add("0");
                    }
                } //end inner for              
            }//end for            
        } catch (Exception exc) {
            log.error("ContentID" + seqid, exc);
        }
        return arrList;
    }//end method

    /**
     * <pre>
     * BY: Aashiq Bhatti
     * </pre>
     * @param contentID
     * @param gsmno
     * @param brand
     * @param model
     * @return
     */
    public int saveDownload(String contentID, String gsmno, String brand, String model) {
        return saveDownload(contentID, gsmno, brand, model, "0");
    }

    public int saveDownload(String contentID, String gsmno, String brand, String model, String TID) {
        return saveDownload(contentID, gsmno, brand, model, TID, "");
    }

    /**
     * <pre>
     * Saves the content in DB, which will be downloaded later.
     * BY: Usman Malik
     * </pre>
     * @param contentID
     * @param gsmno
     * @param brand
     * @param model
     * @param TID
     * @return
     */
    public int saveDownload(String contentID, String gsmno, String brand, String model, String TID, String Operator) {
        int saved = 1;
        try {
            DBProc dbp = DBProc.getInstance();
            HashMap hm = new HashMap();
            Vector v;
            hm.put("ContentID", contentID);
            hm.put("GSM", gsmno);
            hm.put("brand", brand);
            hm.put("model", model);
            hm.put("Operator", Operator);
            if (TID == null || TID.trim().equalsIgnoreCase("")) {
                TID = "0";
            }
            hm.put("TID", TID);

            v = dbp.executeSQLMethod("SAVEDOWNLOAD", hm);

        } catch (Exception exc) {
            saved = -1;
            log.error("Exception in WapPortalBean.saveDownload(" + contentID + "," + gsmno + "," + brand + "," + model + "," + TID + ") : ", exc);
        }
        return saved;

    } //end method savedownloads

    /**
     * <pre>
     * Update the download times of a content in Content table.
     * </pre>
     * @param contentID
     * @return
     */
    public boolean recordDownload(String contentID) {
        HashMap hm = new HashMap();
        hm.put("ContentId", contentID);

        return execNonQuery("recordDownload(" + contentID + ")", "UPDATEDOWNLOADCOUNTS", hm);
    }

    /**
     * 
     * @param contentID
     * @param msisdn
     * @param categoryType
     * @return
     */
    public int getDownloadedStatus(String contentID, String categoryType, String msisdn) {
        if (contentID == null || contentID.length() < 1 || msisdn == null || msisdn.length() < 1) {
            return -2;
        }

        int count = 0;
        try {
            DBProc dbp = DBProc.getInstance();
            HashMap hm = new HashMap();
            Vector v;

            hm.put("ContentID", contentID);
            hm.put("CategoryType", categoryType);
            hm.put("GSM", msisdn);
            //hm.put("Operator", operator);

            v = dbp.executeSQLMethod("GETDOWNLOADSTATUS", hm);
            v = (Vector) v.get(0);
            if (v != null && v.size() == 1) {
                String countStr = v.get(0).toString();
                try {
                    count = Integer.parseInt(countStr);
                } catch (Exception e) {
                    log.error("Exception Parse String to Integer", e);
                    count = -1;
                }
            } else {
                count = -1;
            }
        } catch (Exception exc) {
            log.error("Content Download not Available: Exception in getDownloadedStatus at WapPortalBean Class at WapPortal : ", exc);
            count = -1;
        }
        /*        
        try {
        DBProc dbp = DBProc.getInstance();
        HashMap hm = new HashMap();
        Vector v;

        hm.put("arg1", "-404");
        hm.put("arg2", msisdn);
        hm.put("arg3", categoryType);

        v = dbp.executeSQLMethod("GETDOWNLOADSTATUS", hm);

        if (((Integer) ((Vector) v.get(0)).get(0)).intValue() < 2) {
        return true;
        }
        }
        catch (Exception exc) {
        log.error("Content Download not Available: Exception in getDownloadedStatus at WapPortalBean Class at WapPortal : ", exc);
        }
         */
        log.debug("download status: " + count);
        return count;
    }//end getDownlaodStatus

    /**
     * <pre>
     * to get the file name of games and themese from HANDSETCONTETN table
     * </pre>
     * @param seqid
     * @return
     */
    public String getContentFile(String seqid) {

        ArrayList arrList = new ArrayList();
        String contentFile = "";
        try {
            DBProc dbp = DBProc.getInstance();
            HashMap hm = new HashMap();
            Vector v;

            hm.put("SeqId", seqid);
            arrList = getQueryResult("GETCONTENTFILE", hm);
            if (arrList.size() > 0) {
                contentFile = arrList.get(0).toString();
            }

        } catch (Exception exc) {
            log.error("Exception at getContentFile at WapPortalBean Class at WapPortal : ", exc);
        }
        return contentFile;
    }//end getfile

    /**
     * method to get ArrayList of query
     * @param query
     * @param hm
     * @return
     */
    public ArrayList getQueryResult(String query, HashMap hm) {

        ArrayList arrList = new ArrayList();
        try {
            DBProc dbp = DBProc.getInstance();
            Vector v;

            v = dbp.executeSQLMethod(query, hm);
            if (v == null) {
                return arrList;
                //log.debugln("Vector v getQueryResults: "+v.size()+":"+v+":"+query);
            }
            for (int i = 0; i < v.size(); i++) {

                Vector tmp = (Vector) v.get(i);

                //log.debugln("tmp getQueryResults: "+i+ ":" +tmp);
                for (int j = 0; j < tmp.size(); j++) {
                    if (tmp.get(j) != null) {

                        String value = tmp.get(j).toString();
                        ////log.debugln("value getQueryResults : "+value);
                        if (value != null && !value.equalsIgnoreCase("")) {
                        } else {
                            value = "0";
                        }
                        arrList.add(value);
                    } else {
                        arrList.add("0");
                    }
                }
            }
        } catch (Exception exc) {
            log.error("Exception at getQueryResults() at WapPortalBean Class at WapPortal", exc);
        }
        return arrList;
    }

    /**
     * 
     * @param contentid
     * @return
     */
    public ArrayList getAspectRatios(String contentid) {

        ArrayList arrList = new ArrayList();

        try {

            DBProc dbp = DBProc.getInstance();
            HashMap hm = new HashMap();
            Vector v;

            hm.put("contentID", contentid);

            arrList = getQueryResult("GETASPECTRATIOS", hm);

        } catch (Exception exc) {
            log.error("Exception at getOriginalPath() at WapPortalBean Class at WapPortal : ", exc);
        }
        return arrList;
    }

    /**
     * 
     * @param categoryid
     * @return
     */
    public String getOriginalPath(String categoryid) {

        ArrayList arrList = new ArrayList();
        String originalPath = "";
        try {
            DBProc dbp = DBProc.getInstance();
            HashMap hm = new HashMap();
            Vector v;

            hm.put("categoryID", categoryid);

            arrList = getQueryResult("GETORIGINALPATH", hm);
            if (arrList.size() > 0) {
                originalPath = arrList.get(0).toString();
            }

        } catch (Exception exc) {
            log.error("Exception at getOriginalPath() at WapPortalBean Class at WapPortal : ", exc);
        }
        return originalPath;
    }//end getOriginalPath

    /**
     * 
     * @param id
     * @param type
     * @param price
     * @param directory
     * @param provider
     * @param gsm
     * @param status
     * @param brand
     * @param model
     * @param categoryid
     * @param categoryname
     * @param IP
     * @return
     */    //  public int updDownload(String id,String type,String price,String directory,String provider,String gsm,String status,String brand,String model,String categoryid,String categoryname,String IP)
    public int updDownload(String contentID, String gsm) {
        int saved = 0;
        try {
            DBProc dbp = DBProc.getInstance();
            HashMap hm = new HashMap();
            Vector v;

            hm.put("ContentID", contentID);
            hm.put("GSM", gsm);
            v = dbp.executeSQLMethod("UPDOWNLOAD", hm);
            saved = (Integer) v.get(1);


        } catch (Exception exc) {
            log.error("Exception in updDownload at WapPortalBean Class at WapPortal : ", exc);
        }
        return saved;
    }

    public Vector getContentDownloadParams(String content, String Category, String Type, String msisdn) {
        Vector vec = new Vector();
        ArrayList arrResults = new ArrayList();

        String brand = "";
        String model = "";

        String format = "";
        String price = "";
        String seqid = "";
        String gtseqid = "";
        String originalfile = "";
        String cextension = "";

        HashMap hm = new HashMap();
        Vector v;
        String query = "";
        String filename = "";
        try {
            brand = wurflDevice.getCapability("brand_name");
            model = wurflDevice.getCapability("model_name");

            String blWav = wurflDevice.getCapability("wav");
            String blMmf = wurflDevice.getCapability("mmf");
            String blMidiPoly = wurflDevice.getCapability("ringtone_midi_polyphonic");
            String blSpMidi = wurflDevice.getCapability("sp_midi");
            String blPolyChannels = wurflDevice.getCapability("voices");
            int intPolyChannels = Integer.parseInt(blPolyChannels);

            String blMP3 = wurflDevice.getCapability("mp3");
            String blAMR = wurflDevice.getCapability("amr");
            String width = wurflDevice.getCapability("max_image_width");
            String height = wurflDevice.getCapability("max_image_height");

            width = width.trim();
            height = height.trim();

            int wid = Integer.parseInt(width);
            int ht = Integer.parseInt(height);

            /////////////////////////////////////START POLY/////////////////////////
            if (Type.equalsIgnoreCase("POLYTONES")) {
                hm.put("contentID", content);
                hm.put("categoryID", Category);
                query = "GETTONEFORMATS";
                arrResults = getQueryResult(query, hm);
                if (intPolyChannels >= 40) {
                    for (int i = 0; i < arrResults.size(); i += 4) {
                        if (arrResults.get(i + 3).toString().equalsIgnoreCase("PolyMidi40")) {
                            seqid = arrResults.get(i).toString();
                            originalfile = arrResults.get(i + 1).toString();
                            cextension = originalfile.substring(originalfile.indexOf("."), originalfile.length());
                            price = arrResults.get(i + 2).toString();
                            format = "PolyMidi40";
                            i = arrResults.size();
//                            polyURL = startContent + price + "/" + seqid + cextension;
                            vec.addElement("price");
                            vec.addElement(price);
                            vec.addElement("fn");
                            vec.addElement(seqid + cextension);
                        } else if (arrResults.get(i + 3).toString().equalsIgnoreCase("PolyMidi16")) {
                            seqid = arrResults.get(i).toString();
                            originalfile = arrResults.get(i + 1).toString();
                            cextension = originalfile.substring(originalfile.indexOf("."), originalfile.length());
                            price = arrResults.get(i + 2).toString();
                            format = "PolyMidi16";
                            i = arrResults.size();
//                            polyURL = startContent + price + "/" + seqid + cextension;
                            vec.addElement("price");
                            vec.addElement(price);
                            vec.addElement("fn");
                            vec.addElement(seqid + cextension);
                        } else if (arrResults.get(i + 3).toString().equalsIgnoreCase("PolyMidi4")) {
                            seqid = arrResults.get(i).toString();
                            originalfile = arrResults.get(i + 1).toString();
                            cextension = originalfile.substring(originalfile.indexOf("."), originalfile.length());
                            price = arrResults.get(i + 2).toString();
                            format = "PolyMidi4";
                            i = arrResults.size();
//                            polyURL = startContent + price + "/" + seqid + cextension;
                            vec.addElement("price");
                            vec.addElement(price);
                            vec.addElement("fn");
                            vec.addElement(seqid + cextension);
                        } else if (arrResults.get(i + 3).toString().equalsIgnoreCase("PolySpMidi")) {
                            seqid = arrResults.get(i).toString();
                            originalfile = arrResults.get(i + 1).toString();
                            cextension = originalfile.substring(originalfile.indexOf("."), originalfile.length());
                            price = arrResults.get(i + 2).toString();
                            format = "PolySpMidi";
                            i = arrResults.size();
//                            polyURL = startContent + price + "/" + seqid + cextension;
                            vec.addElement("price");
                            vec.addElement(price);
                            vec.addElement("fn");
                            vec.addElement(seqid + cextension);
                        }
                    }//end else for
                } else if (intPolyChannels >= 16) {
                    for (int i = 0; i < arrResults.size(); i += 4) {
                        if (arrResults.get(i + 3).toString().equalsIgnoreCase("PolyMidi16")) {
                            seqid = arrResults.get(i).toString();
                            originalfile = arrResults.get(i + 1).toString();
                            cextension = originalfile.substring(originalfile.indexOf("."), originalfile.length());
                            price = arrResults.get(i + 2).toString();
                            format = "PolyMidi16";
                            i = arrResults.size();
//                            polyURL = startContent + price + "/" + seqid + cextension;
                            vec.addElement("price");
                            vec.addElement(price);
                            vec.addElement("fn");
                            vec.addElement(seqid + cextension);
                        } else if (arrResults.get(i + 3).toString().equalsIgnoreCase("PolyMidi4")) {
                            seqid = arrResults.get(i).toString();
                            originalfile = arrResults.get(i + 1).toString();
                            cextension = originalfile.substring(originalfile.indexOf("."), originalfile.length());
                            price = arrResults.get(i + 2).toString();
                            format = "PolyMidi4";
                            i = arrResults.size();
//                            polyURL = startContent + price + "/" + seqid + cextension;
                            vec.addElement("price");
                            vec.addElement(price);
                            vec.addElement("fn");
                            vec.addElement(seqid + cextension);
                        } else if (arrResults.get(i + 3).toString().equalsIgnoreCase("PolySpMidi")) {
                            seqid = arrResults.get(i).toString();
                            originalfile = arrResults.get(i + 1).toString();
                            cextension = originalfile.substring(originalfile.indexOf("."), originalfile.length());
                            price = arrResults.get(i + 2).toString();
                            format = "PolySpMidi";
                            i = arrResults.size();
//                            polyURL = startContent + price + "/" + seqid + cextension;
                            vec.addElement("price");
                            vec.addElement(price);
                            vec.addElement("fn");
                            vec.addElement(seqid + cextension);
                        }
                    }
                } else if (blSpMidi.equals("false") && intPolyChannels >= 4 && intPolyChannels < 16) {
                    for (int i = 0; i < arrResults.size(); i += 4) {
                        if (arrResults.get(i + 3).toString().equalsIgnoreCase("PolyMidi4")) {
                            seqid = arrResults.get(i).toString();
                            originalfile = arrResults.get(i + 1).toString();
                            cextension = originalfile.substring(originalfile.indexOf("."), originalfile.length());
                            price = arrResults.get(i + 2).toString();
                            format = "PolyMidi4";
                            i = arrResults.size();
//                            polyURL = startContent + price + "/" + seqid + cextension;
                            vec.addElement("price");
                            vec.addElement(price);
                            vec.addElement("fn");
                            vec.addElement(seqid + cextension);
                        } else if (arrResults.get(i + 3).toString().equalsIgnoreCase("PolySpMidi")) {
                            seqid = arrResults.get(i).toString();
                            originalfile = arrResults.get(i + 1).toString();
                            cextension = originalfile.substring(originalfile.indexOf("."), originalfile.length());
                            price = arrResults.get(i + 2).toString();
                            format = "PolySpMidi";
                            i = arrResults.size();
//                            polyURL = startContent + price + "/" + seqid + cextension;
                            vec.addElement("price");
                            vec.addElement(price);
                            vec.addElement("fn");
                            vec.addElement(seqid + cextension);
                        }
                    }
                } else if (blSpMidi.equals("true")) {//&& intPolyChannels == 4 ){
                    for (int i = 0; i < arrResults.size(); i += 4) {
                        if (arrResults.get(i + 3).toString().equalsIgnoreCase("PolySpMidi")) {
                            seqid = arrResults.get(i).toString();
                            originalfile = arrResults.get(i + 1).toString();
                            cextension = originalfile.substring(originalfile.indexOf("."), originalfile.length());
                            price = arrResults.get(i + 2).toString();
                            format = "PolySpMidi";
                            i = arrResults.size();
//                            polyURL = startContent + price + "/" + seqid + cextension;
                            vec.addElement("price");
                            vec.addElement(price);
                            vec.addElement("fn");
                            vec.addElement(seqid + cextension);
                        }
                    }
                } else if (blMmf.equals("true")) {
                    for (int i = 0; i < arrResults.size(); i += 4) {
                        if (arrResults.get(i + 3).toString().equalsIgnoreCase("PolyMMF")) {
                            seqid = arrResults.get(i).toString();
                            originalfile = arrResults.get(i + 1).toString();
                            cextension = originalfile.substring(originalfile.indexOf("."), originalfile.length());
                            price = arrResults.get(i + 2).toString();
                            format = "PolyMMF";
                            i = arrResults.size();
//                            polyURL = startContent + price + "/" + seqid + cextension;
                            vec.addElement("price");
                            vec.addElement(price);
                            vec.addElement("fn");
                            vec.addElement(seqid + cextension);
                        }//end if inner                        
                    }//end for out                                     
                } else {
//                    polyURL = "0";
                }
//                arrList.add(polyURL);

                ////////////////////////////////END POLY///////////////////////////////
            } else if (Type.equalsIgnoreCase("TRUETONES")) {
                hm.put("contentID", content);
                hm.put("categoryID", Category);
                query = "GETTONEFORMATS";
                arrResults = getQueryResult(query, hm);
                if (blMP3.equals("true")) {
                    for (int i = 0; i < arrResults.size(); i += 4) {
                        if (arrResults.get(i + 3).toString().equalsIgnoreCase("MP3")) {
                            seqid = arrResults.get(i).toString();
                            originalfile = arrResults.get(i + 1).toString();
                            cextension = originalfile.substring(originalfile.indexOf("."), originalfile.length());
                            price = arrResults.get(i + 2).toString();
                            format = "MP3";
                            i = arrResults.size();
//                            trueURL = startContent + price + "/" + seqid + cextension;
                            vec.addElement("price");
                            vec.addElement(price);
                            vec.addElement("fn");
                            vec.addElement(seqid + cextension);
                        }
                    }
                } else if (blAMR.equals("true")) {
                    for (int i = 0; i < arrResults.size(); i += 4) {
                        if (arrResults.get(i + 3).toString().equalsIgnoreCase("AMR")) {
                            seqid = arrResults.get(i).toString();
                            originalfile = arrResults.get(i + 1).toString();
                            cextension = originalfile.substring(originalfile.indexOf("."), originalfile.length());
                            price = arrResults.get(i + 2).toString();
                            format = "AMR";
                            i = arrResults.size();
//                            trueURL = startContent + price + "/" + seqid + cextension;
                            vec.addElement("price");
                            vec.addElement(price);
                            vec.addElement("fn");
                            vec.addElement(seqid + cextension);
                        }
                    }
                } else {
//                    trueURL = "0";
                }
//                arrList.add(trueURL);
            } else if (Type.equalsIgnoreCase("WALLPAPERS")) {
                hm.put("contentID", content);
                hm.put("categoryID", Category);
                query = "GETAW";
                arrResults = getQueryResult(query, hm);
                if (arrResults.size() >= 3) {
                    seqid = arrResults.get(0).toString();
                    originalfile = arrResults.get(1).toString();
                    cextension = originalfile.substring(originalfile.indexOf("."), originalfile.length());
                    price = arrResults.get(2).toString();
                }
//                contentUrl = startContent + price + "/w_" + wid + "/h_" + ht + "/" + seqid + cextension;
//              arrList.add(contentUrl);
                vec.addElement("price");
                vec.addElement(price);
                vec.addElement("w");
                vec.addElement(wid);
                vec.addElement("h");
                vec.addElement(ht);
                vec.addElement("fn");
                vec.addElement(seqid + cextension);
            } else if (Type.equalsIgnoreCase("ANIMATIONS") || Type.equalsIgnoreCase("ANIMATED")) {
                hm.put("contentID", content);
                hm.put("categoryID", Category);
                query = "GETAW";
                arrResults = getQueryResult(query, hm);
                if (arrResults.size() >= 3) {
                    seqid = arrResults.get(0).toString();
                    originalfile = arrResults.get(1).toString();
                    cextension = originalfile.substring(originalfile.indexOf("."), originalfile.length());
                    price = arrResults.get(2).toString();
                }
//                contentUrl = startContent + price + "/w_" + wid + "/h_" + ht + "/" + seqid + cextension;
//                arrList.add(contentUrl);
                vec.addElement("price");
                vec.addElement(price);
                vec.addElement("w");
                vec.addElement(wid);
                vec.addElement("h");
                vec.addElement(ht);
                vec.addElement("fn");
                vec.addElement(seqid + cextension);
            } else if (Type.equalsIgnoreCase("GAME")) {
                hm.put("contentID", content);
                hm.put("categoryID", Category);
                hm.put("brand", brand);
                hm.put("model", model);
                query = "GETGT";
                arrResults = getQueryResult(query, hm);
                if (arrResults.size() >= 4) {
                    gtseqid = arrResults.get(0).toString();
                    seqid = arrResults.get(2).toString();
                    originalfile = arrResults.get(3).toString();
                    cextension = originalfile.substring(originalfile.indexOf("."), originalfile.length());
                    price = arrResults.get(1).toString();
                }
//                contentUrl = startContent + price + "/g_" + seqid + "/" + gtseqid + cextension;
//                arrList.add(contentUrl);
                vec.addElement("price");
                vec.addElement(price);
                vec.addElement("g");
                vec.addElement(seqid);
                vec.addElement("fn");
                vec.addElement(gtseqid + cextension);
            } else if (Type.equalsIgnoreCase("THEMES")) {
                hm.put("contentID", content);
                hm.put("categoryID", Category);
                hm.put("brand", brand);
                hm.put("model", model);
                query = "GETGT";
                arrResults = getQueryResult(query, hm);
                if (arrResults.size() >= 4) {
                    gtseqid = arrResults.get(0).toString();
                    seqid = arrResults.get(2).toString();
                    originalfile = arrResults.get(3).toString();
                    cextension = originalfile.substring(originalfile.indexOf("."), originalfile.length());
                    price = arrResults.get(1).toString();
                }
//                contentUrl = startContent + price + "/g_" + seqid + "/" + gtseqid + cextension;
//                arrList.add(contentUrl);
                vec.addElement("price");
                vec.addElement(price);
                vec.addElement("g");
                vec.addElement(seqid);
                vec.addElement("fn");
                vec.addElement(gtseqid + cextension);
            } else if (Type.equalsIgnoreCase("SCREENSAVERS")) {
                hm.put("contentID", content);
                hm.put("categoryID", Category);
                hm.put("brand", brand);
                hm.put("model", model);
                query = "GETGT";
                arrResults = getQueryResult(query, hm);
                if (arrResults.size() >= 4) {
                    gtseqid = arrResults.get(0).toString();
                    seqid = arrResults.get(2).toString();
                    originalfile = arrResults.get(3).toString();
                    cextension = originalfile.substring(originalfile.indexOf("."), originalfile.length());
                    price = arrResults.get(1).toString();
                }
//                contentUrl = startContent + price + "/g_" + seqid + "/" + gtseqid + cextension;
//                arrList.add(contentUrl);
                vec.addElement("price");
                vec.addElement(price);
                vec.addElement("g");
                vec.addElement(seqid);
                vec.addElement("fn");
                vec.addElement(gtseqid + cextension);
            } else if (Type.equalsIgnoreCase("VIDEOS")) {
                hm.put("contentID", content);
                hm.put("categoryID", Category);
                query = "GETVDO";
                arrResults = getQueryResult(query, hm);
                if (arrResults.size() >= 3) {
                    seqid = arrResults.get(0).toString();
                    originalfile = arrResults.get(1).toString();
                    cextension = originalfile.substring(originalfile.indexOf("."), originalfile.length());
                    price = arrResults.get(2).toString();
                }
//                contentUrl = startContent + price + "/" + seqid + cextension;
//                arrList.add(contentUrl);
                vec.addElement("price");
                vec.addElement(price);
                vec.addElement("fn");
                vec.addElement(seqid + cextension);
            }
        } catch (Exception exc) {
            log.error("Exception at getContentURL() at WapPortalBean Class at WapPortal", exc);
        }
        /*
        if (arrList.size() > 0 && ((String) arrList.get(0)).contains("_/")) {
        StringBuffer sbuf = new StringBuffer();
        if (seqid == null || seqid.length() < 1) {
        hm.put("brand", brand);
        hm.put("model", model);
        query = "GETGTALTERNATE";
        arrResults = getQueryResult(query, hm);
        sbuf.append("HTML:");
        //if(arrResults.size()>=4){
        //log.debugln("ALT:"+arrResults);
        for (int idx = 0; idx < arrResults.size(); idx += 5) {
        gtseqid = arrResults.get(idx + 0).toString();
        price = arrResults.get(idx + 1).toString();
        String id = arrResults.get(idx + 2).toString();
        String contentN = arrResults.get(idx + 3).toString();
        Type = arrResults.get(idx + 4).toString();
        sbuf.append(Type + " <a href=\"/telenor" + (getContentURL(gtseqid, id, Type, ua)).get(0).toString() + "\">" + contentN + "</a><br>");
        }
        }
        hm.put("contentID", content);
        hm.put("GSM", msisdn);
        query = "MGCDOWNLOAD";
        getQueryResult(query, hm);
        arrList = new ArrayList();
        arrList.add(sbuf.toString());
        }
         */
        return vec;
    }

    // </editor-fold>
    // <editor-fold defaultstate="collapsed" desc="Functions related to SendXMS">
    public int saveXMSWAP(String sendxmswap, String providerName) {
        int saved = 1;
        try {
            HashMap hm = new HashMap();
            Vector v;
            hm.put("sendxmswap", sendxmswap);
            hm.put("providerName", providerName);
            v = this.execCustomQuery("SAVESENDXMSWAP", hm);
        } catch (Exception exc) {
            log.error("Exception insaveXMSWAP: ", exc);
            return -1;
        }
        return saved;
    }//end method savedownloads

    /**
     * 
     * @param sendxms
     * @param providerName
     * @return
     */
    public int saveXMS(String sendxms, String providerName) {
        int saved = 1;
        try {
            HashMap hm = new HashMap();
            Vector v;
            hm.put("sendxms", sendxms);
            hm.put("providerName", providerName);
            v = this.execCustomQuery("SAVESENDXMS", hm);
        } catch (Exception exc) {
            log.error("Exception insaveXMS: ", exc);
            return -1;
        }
        return saved;
    }//end method savedownloads

    // </editor-fold>
    public int getProportionalHeight(int actWidth, int width, int actHeight) {
        return (int) Math.ceil(((double) width / (double) actWidth) * (double) actHeight);
    }

    public Vector fetchSpecificContent(String Categories, HashMap $hmparam) {
        Vector $v = new Vector();
        int categoryCount = 0;
        String[] databaseCategories = {"TRUETONES", "WALLPAPERS", "VIDEOS", "ANIMATED", "GAMES", "THEMES", "TRAILERVIDEOS"};
        StringBuffer query = new StringBuffer();
        String strTemp = "";
        String innerQuery = "(SELECT Distinct ContentID as contid, ContentName as contname, ContentImage as contimg, "
                + "Price as contprice, ServiceURL as contserURL, jspf AS JSP, html AS HTM, Parameters as params, Status, "
                + "SingerOrFilm, CategoryType As ContType, CategoryId As CategoryID, MainCat As MainCat, ContentSequence, "
                + "orderSequence FROM content WHERE (Tags LIKE '%${Tags}%' OR ContentName LIKE '%${Tags}%') "
                + "AND categorytype like '%${CategoryType}%' AND MainCat like '%${MainCat}%' "
                + "AND SingerOrFilm like '%${SingerOrFilm}%' AND Status = 1 AND IsAuthorized = 1 "
                + "ORDER BY ContentSequence DESC LIMIT ${Dynamic_Limit}) ";

        String[] categoryList = Categories.split(",");
        
        for (int i = 0; i < categoryList.length; i++) {
            if (wap.rockvilletech.util.StringUtils.isInList(databaseCategories, categoryList[i].trim())) {
                if (categoryCount > 0) {
                    query.append(" UNION ALL ");
                }
                query.append(innerQuery.replace("${CategoryType}", categoryList[i]).trim());
                categoryCount++;
            }
        }

        strTemp = query.toString();
        query = null;

        if (strTemp.length() > 100) {
            query = new StringBuffer();
            query.append("SELECT * FROM ( ");
            query.append(strTemp);
            query.append(") as topcontent");
//            query.append(") AS res ORDER BY orderSequence ASC, ContentSequence DESC ");

            return execQuery_Direct("", query.toString(), $hmparam);
        } else {
            return $v;
        }
    }

    public String[] getRandomBanner(String tags) {
        String[] arrBanner = null;
        Connection con = null;
        DBProc db = DBProc.getInstance();
        try {
            String strQuery = "SELECT SeqID, AddName, AddGraphics, Jspf, Tags FROM adds WHERE Status = 1 AND IsBanner = 1 AND Tags like ?  order by RAND() LIMIT 1";
            con = db.getJNDIConnection();
            PreparedStatement pst = con.prepareStatement(strQuery);
            pst.setString(1, "%" + tags + "%");
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                arrBanner = new String[]{rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5),};
            }
            rs.close();
            pst.close();
        } catch (Exception ex) {
            log.error("getRandomBanner()--", ex);
            arrBanner = null;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    log.error(" getRandomBanner()--  Bb Connetion close exception -", ex);
                }
            }
        }
        return arrBanner;
    }

    public String coolStuffMapping(String stuff) {
        String strReturn = "";
        try {//all horoscope assign no is dependent on Db so you can't change.
            int i = Integer.parseInt(stuff);
            if (1 == i) {
                strReturn = "Jokes";
            } else if (56 == i) {
                strReturn = "Love Quotes";
            } else if (2 == i) {
                strReturn = "Love Match";
            } else if (3 == i) {
                strReturn = "Born Today";
            } else if (4 == i) {
                strReturn = "Top Hollywood Movies";
            } else if (5 == i) {
                strReturn = "Top Bollywood Movies";
            } else if (6 == i) {
                strReturn = "Top Music Charts English";
            } else if (7 == i) {
                strReturn = "Top Music Charts Urdu";
            } else if (8 == i) {
                strReturn = "Hollywood Gossip";
            } else if (9 == i) {
                strReturn = "Bollywood Gossip";
            } else if (10 == i) {
                strReturn = "Lollywood Gossip";
            } else if (57 == i) {
                strReturn = "Famous Quotes";
            } else if (52 == i) {
                strReturn = "Latest News";
            } else if (54 == i) {
                strReturn = "Urdu News";
            } else if (53 == i) {
                strReturn = "Sports News";
            } else if (58 == i) {
                strReturn = "LifeStyle News";
            } else if (59 == i) {
                strReturn = "Cricket News";
            } else if (55 == i) {
                strReturn = "Finance News";
            } else if (37 == i) {
                strReturn = "Aries";
            } else if (38 == i) {
                strReturn = "Taurus";
            } else if (39 == i) {
                strReturn = "Gemini";
            } else if (40 == i) {
                strReturn = "Cancer";
            } else if (41 == i) {
                strReturn = "Leo";
            } else if (42 == i) {
                strReturn = "Virgo";
            } else if (43 == i) {
                strReturn = "Libra";
            } else if (44 == i) {
                strReturn = "Scorpio";
            } else if (45 == i) {
                strReturn = "Sagittarius";
            } else if (46 == i) {
                strReturn = "Capricorn";
            } else if (47 == i) {
                strReturn = "Aquarius";
            } else if (48 == i) {
                strReturn = "Pisces";
            }

        } catch (Exception e) {
            log.error("coolStuffMapping() = " + stuff, e);
            strReturn = "";
        }
        return strReturn;
    }

    /**
     * <pre>
     * Logs the legitimate visit to DB
     * </pre>
     * @param IP
     * @param UA
     * @param Type
     * @param GSM
     * @param APN
     * @param Brand
     * @param Model
     * @param AppName
     * @param design
     * @param navBar
     */
    public void LogVisit(String IP, String Page, String GSM, String APN, String Brand, String Model, String AppName, String queryString) {
        Connection con = null;
        DBProc db = DBProc.getInstance();
        try {
            String strQuery = "Insert into hits(IP,Page, AppName, QueryString, Brand, Model,GSM) values(?,?,?,?,?,?,?)";
            con = db.getJNDIConnection();
            PreparedStatement pst = con.prepareStatement(strQuery);
            pst.setString(1, IP);
            pst.setString(2, Page);
            pst.setString(3, AppName);
            pst.setString(4, queryString);
            pst.setString(5, Brand);
            pst.setString(6, Model);
            pst.setString(7, GSM);
            pst.executeUpdate();
            pst.close();
        } catch (Exception e) {
            log.error("LogVisits(" + IP + ", " + Page + ", " + GSM + ", " + APN + ", " + Brand + ", " + Model + ", " + AppName + ", " + queryString + ") ", e);
        } finally {
            db.closeJNDIConnection(con);
        }
    }

    public int persistContentCharging(String contentId, String categoryId, String contentType, String queryString, String shortcode, String msisdn) {
        Connection con = null;
        DBProc db = DBProc.getInstance();
        int lastInsertId = 0;
        try {//id, ip, app, price, query_string, tstamp
            String strQuery = "insert into content_charge(content_id, category_id, category_type, query_string, shortcode, msisdn, tstamp) values(?,?,?,?,?,?,now())";
            con = db.getJNDIConnection();
            PreparedStatement pst = con.prepareStatement(strQuery);
            pst.setString(1, contentId);
            pst.setString(2, categoryId);
            pst.setString(3, contentType);
            pst.setString(4, queryString);
            pst.setString(5, shortcode);
            pst.setString(6, msisdn);
            pst.executeUpdate();            
            pst.close();            
            try {
	            pst = con.prepareStatement("select LAST_INSERT_ID()");
	            ResultSet rs = pst.executeQuery();
	            if(rs.next()) {
	            	lastInsertId = rs.getInt(1);
	            }
	            rs.close();
	            pst.close();
            } catch (Exception e) {
	                log.error("Error getting lastInsertID, LogPrice(" + contentId + ", " + categoryId+ ", " + contentType+ ", " + queryString + ", " + shortcode+ ", " + msisdn+ ") ", e);
            }
        } catch (Exception e) {
            log.error("LogPrice(" + contentId + ", " + categoryId+ ", " + contentType+ ", " + queryString + ", " + shortcode+ ", " + msisdn+ ") ", e);
        } finally {
            db.closeJNDIConnection(con);
        }
        return lastInsertId;
    }

    public String[] getContentChargingRecord(String seqid) {
        String[] record = null;
        Connection con = null;
        DBProc db = DBProc.getInstance();
        try {
            String strQuery = "SELECT content_id, category_id, category_type, price, shortcode, msisdn from content_charge where seqid=?";
            con = db.getJNDIConnection();
            PreparedStatement pst = con.prepareStatement(strQuery);
            pst.setInt(1, Integer.parseInt(seqid.trim()));
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
            	record = new String[]{rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6)};
            }
            rs.close();
            pst.close();
        } catch (Exception ex) {
            log.error("getShortCodeReplyData("+seqid+") ", ex);
            record = null;
        } finally {
        	db.closeJNDIConnection(con);
        }
        return record;
    }

    public void setContentDownloadStatus(String logId, String price, String ip, String brand, String model) {
        try {
            DBProc dbp = DBProc.getInstance();
            HashMap hm = new HashMap();
            Vector v;
            hm.put("price", price);
            hm.put("ip", ip);
            hm.put("brand", brand);
            hm.put("model", model);
            hm.put("logId", logId);
            v = dbp.executeSQLMethod("UPCHARGE_DLOAD_STATUS", hm);
        } catch (Exception exc) {
            log.error("updateLogPriceStatus("+logId+")", exc);
        }
    }    
    
    public Vector<String[]> get_latest_worldcup_fixtures(boolean isTop,String strSeries) {
        Connection conn = null;
        Vector<String[]> v_return = new Vector<String[]>();
        DBProc dbproc=DBProc.getInstance();
        String strQuery="SELECT concat(date_format(startdate,'%d %M %Y (%a) '), starttime) , title,(Select abbr from cric_team where id=hometeam), (Select abbr from cric_team where id=awayteam), venuename FROM cric_match where series like ? and date_format(startdate,'%Y-%m-%d')>=date_format(now(),'%Y-%m-%d') order by startdate";
        try {
            if(isTop){
            	strQuery += " LIMIT 2";
            }
            conn = dbproc.getJNDIConnection();
            PreparedStatement pst = conn.prepareStatement(strQuery);
            pst.setString(1, "%"+strSeries+"%");
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                v_return.add(new String[]{rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5)});
            }
            rs.close();
            pst.close();

        } catch (Exception e) {
            log.error("get_top_worldcup_news()=", e);
        } finally {
            dbproc.closeJNDIConnection(conn);
        }
        return v_return;
    }

    public String getShortCodeOnPrice(int pce) {
    	String port = "";
    	switch(pce) {
		case 5:
			port = "6212";
			break;
		case 10:
			port = "6213";
			break;
		case 15:
			port = "6214";
			break;
		case 20:
			port = "6215";
			break;
		case 35:
			port = "6216";
			break;
    	}
    	return port;
    }
    public int setUtuneSubscription(String gsm,String rbtId,String securityCode) {
        Connection conn = null;
        int intResponse=0;
        DBProc dbproc=DBProc.getInstance();
        String strQuery="";
        try {
            strQuery="insert into utune_subscriber(msisdn,utune_subscribed,sms_code,used_tstmap) values(?,?,?,NOW())";
            conn = dbproc.getJNDIConnection();
            PreparedStatement pst = conn.prepareStatement(strQuery);
            pst.setString(1, gsm);
            pst.setString(2, rbtId);
            pst.setString(3, securityCode);
            intResponse = pst.executeUpdate();
            pst.close();

        } catch (Exception e) {
            log.error("setUtuneSubscription("+gsm+" ,"+rbtId+" ,"+securityCode+")=", e);
        } finally {
            dbproc.closeJNDIConnection(conn);
        }
        return intResponse;
    }
}
